Skip Headers

Oracle® Database SQL Reference 10g Release 2 (10.2) Part Number B14200-02 |
Home | Book List | Contents | Index | Master Index | Feedback

Previous | Next
| View PDF

# SOUNDEX

Syntax

Description of the illustration soundex.gif

Purpose

`SOUNDEX` returns a character string containing the phonetic representation of `char`. This function lets you compare words that are spelled differently, but sound alike in English.

The phonetic representation is defined in The Art of Computer Programming, Volume 3: Sorting and Searching, by Donald E. Knuth, as follows:

1. Retain the first letter of the string and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.

2. Assign numbers to the remaining letters (after the first) as follows:

       b, f, p, v = 1
       c, g, j, k, q, s, x, z = 2
       d, t = 3
       l = 4
       m, n = 5
       r = 6

3. If two or more letters with the same number were adjacent in the original name (before step 1), or adjacent except for any intervening h and w, then omit all but the first.

4. Return the first four bytes padded with 0.

`char` can be of any of the datatypes `CHAR`, `VARCHAR2`, `NCHAR`, or `NVARCHAR2`. The return value is the same datatype as `char`.

This function does not support `CLOB` data directly. However, `CLOB`s can be passed in as arguments through implicit data conversion.

See Also: "Datatype Comparison Rules" for more information.

Examples

The following example returns the employees whose last names are a phonetic representation of "Smyth":

    SELECT last_name, first_name
         FROM hr.employees
         WHERE SOUNDEX(last_name)
             = SOUNDEX('SMYTHE');

    LAST_NAME  FIRST_NAME
    ---------- ----------
    Smith      Lindsey
    Smith      William